
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetLateShippedDateReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetLateShippedDateReport]
GO


CREATE PROCEDURE spShippingStatusReportGetLateShippedDateReport	
@OfficeID int,
@Type int
AS
SELECT ID, REQ, 
		CASE 
			WHEN OrderDate IS NOT NULL AND OrderDate > '1/1/1900' THEN OrderDate
		END OrderDate,			
		ContainerSize, LP, PONo_Office, PONo_Type, PONo_Supplier, PONo_Number, IsPrimary, 
		CASE 
			WHEN PONo_Consolidation <> 0 THEN PONo_Consolidation
		END PONo_Consolidation,		 
		AccNo, CustomerOrLocation, FinalDestination, [Description], 
		CASE 
			WHEN FactoryETD IS NOT NULL AND FactoryETD > '1/1/1900' THEN FactoryETD
		END FactoryETD,			

		CASE 
			WHEN ShippingDate IS NOT NULL AND ShippingDate > '1/1/1900' THEN ShippingDate
		END ShippingDate,			

		ShippingLine, Vessel, ContainerNo, 

		CASE 
			WHEN ETA IS NOT NULL AND ETA > '1/1/1900' THEN ETA
		END ETA,			

		[Week], ArrivalPort, PI, Payment, DocReceived, 
		CASE 
			WHEN CompletedDate IS NOT NULL AND CompletedDate > '1/1/1900' THEN CompletedDate
		END CompletedDate,
		BulkType, 
		CASE 
			WHEN BulkOrderNo <> 0 THEN BulkOrderNo
		END BulkOrderNo,
		[PayAuth],
		CASE 
			WHEN ContainerDeliveried IS NOT NULL AND ContainerDeliveried > '1/1/1900' THEN ContainerDeliveried
		END ContainerDeliveried,
		ModifiedDate,
		getDate() CurrentDate,
		CASE 
			WHEN POInOpera IS NOT NULL AND POInOpera > '1/1/1900' THEN POInOpera
		END POInOpera,	
		CASE 
			WHEN InvValue <> 0 THEN InvValue
		END InvValue,		
		CASE 
			WHEN PayDate IS NOT NULL AND PayDate > '1/1/1900' THEN PayDate
		END PayDate,		
		Remarks,
		SuppOrderNo,
		CASE 
			WHEN SInvoiced <> 0 THEN SInvoiced
		END SInvoiced,
		OnHold,
		[OfficeID],
		[CustPONo],
		[BulkOrderOfficeID],
		[SInvoicedPd],
	[Catalogue],
	TargetETD
FROM
	[ShippingStatusReport] 
WHERE 
(TargetETD is not null AND TargetETD > '1/1/1900')
	AND ((@Type = 0 AND CompletedDate IS NULL) OR @Type=1)
	AND (DATEDIFF(day, TargetETD, getdate()) > 7)
	AND (ShippingDate  is null OR ShippingDate = '1/1/1900')
	AND OfficeID = @OfficeID 
ORDER BY [REQ] asc


GO

	
 